package org.example;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.JsonStreamContext;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.SneakyThrows;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

public class DataTransformationService {
    private static final Log log = LogFactory.getLog(DataTransformationService.class);
    
    private final DatabaseManager databaseManager;
    private final DatabaseManager databaseManager1;
    private final Map<String, TypeConverter> converters = new HashMap<>();
    private final Map<String, Object> cacheMap = new HashMap<>();

    @SneakyThrows
    public DataTransformationService(DatabaseManager databaseManager) {
        this.databaseManager = databaseManager;
        databaseManager1 = new DatabaseManager();
        //databaseManager1.connect("mysql", "10.155.2.17", "13306", "ceprei-cloud-personnel-data", "ceprei_cloud_dev", "Isuperone#123");
        databaseManager1.connect("mysql", "10.155.2.47", "13306", "ceprei-cloud-personnel-data", "microservice", "Isuperone#123");

        // 注册基本类型转换器
        registerConverter("Integer", new IntegerConverter());
        registerConverter("Long", new LongConverter());
        registerConverter("Double", new DoubleConverter());
        registerConverter("BigDecimal", new BigDecimalConverter());
        registerConverter("String", new StringConverter());
        registerConverter("LocalDate", new DateConverter());
        registerConverter("LocalDateTime", new DateTimeConverter());
        registerConverter("Date", new DateTimeConverter());
        registerConverter("Boolean", new BooleanConverter());
        registerConverter("byte[]", new BinaryConverter());
        registerConverter("char[]", new StringConverter());
        registerConverter("Map", new JsonConverter());
        registerConverter("List", new JsonConverter());
        registerConverter("UUID", new StringConverter());
        registerConverter("Time", new TimeConverter());
    }

    public void registerConverter(String type, TypeConverter converter) {
        converters.put(type, converter);
    }


    @SneakyThrows
    public Map<String, Object> transformRow(Map<String, Object> rowData, List<MappingRule> rules) {
        ObjectMapper objectMapper = new ObjectMapper(); // 用于JSON解析
        Map<String, Object> transformedRow = new HashMap<>();
        ArrayList<String> ignoreColumns = ignoreColumns();
        Set<String> targetFieldSet = rules.stream().map(MappingRule::getTargetField).collect(Collectors.toSet());
        for (MappingRule rule : rules) {
            Object sourceValue = rowData.get(rule.getSourceField());
            if (ignoreColumns.contains(rule.getTargetField()))continue;
            if (rule.getForeignKeyTable() != null && !"file".equals(rule.getDefaultValue())) {
                try {
                    sourceValue = getObject(rule, sourceValue, targetFieldSet, transformedRow);
                } catch (SQLException e) {
                    System.err.println("外键处理失败: " + e.getMessage());
                    sourceValue = null;
                }
            }

            // 应用转换规则
            Object targetValue = sourceValue;
            if (sourceValue != null) {
                //处理枚举映射
                if (rule.getEnumMapping() != null && !rule.getEnumMapping().isEmpty()) {
                    @SuppressWarnings("unchecked")
                    Map<String, String> enumMap = objectMapper.readValue(rule.getEnumMapping(), HashMap.class);
                    String sourceKey = sourceValue.toString();
                    if (sourceKey != null && enumMap.containsKey(sourceKey)) {
                        targetValue = enumMap.get(sourceKey);
                    }
                }

                if (rule.getDefaultValue() != null && !rule.getDefaultValue().isEmpty()) {
                    targetValue = rule.getDefaultValue();
                    if (rule.getDefaultValue().equals("uuid()") && rule.getTargetField().equals("id")){
                        targetValue = UUID.randomUUID().toString();
                    }
                    // 当默认值为"md5"时，将源值转换为MD5哈希
                    if (rule.getDefaultValue().equals("md5")) {
                        targetValue = md5Hash(sourceValue.toString());
                    }
                    if (rule.getDefaultValue().equals("file")){
                        String string = sourceValue.toString();
                        String[] split = string.split(",");
                        ArrayList<String> strings = new ArrayList<>();
                        for (String s : split) {
                            Object fileId = databaseManager1.queryForeignKeyLikeValue(rule.getForeignKeyTable(), rule.getForeignKeyColumn(), rule.getForeignKeyTargetColumn(), s);
                            if (fileId != null){
                                strings.add(fileId.toString());
                            }
                        }
                        targetValue = strings.stream().collect(Collectors.joining(","));
                    }
                }

                if ((targetValue instanceof LocalDate || targetValue instanceof LocalDateTime)
                        && rule.getDateFormat() != null && !rule.getDateFormat().isEmpty()) {
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern(rule.getDateFormat());
                    if (targetValue instanceof LocalDate) {
                        targetValue = ((LocalDate) targetValue).format(formatter);
                    } else if (targetValue instanceof LocalDateTime) {
                        targetValue = ((LocalDateTime) targetValue).format(formatter);
                    }
                }

                // 处理类型转换
                if (!rule.getConversionType().equals("String")) {
                    targetValue = convertValue(targetValue, rule.getConversionType());
                }
            }
            transformedRow.put(rule.getTargetField(), targetValue);
            transformedRow.put("create_time", new Date());
        }

        return transformedRow;
    }

    private Object getObject(MappingRule rule, Object sourceValue, Set<String> targetFieldSet, Map<String, Object> transformedRow) throws SQLException {
        if (rule.getForeignKeyTable().equals("remote_personal_employee") && rule.getForeignKeyColumn().equals("id_card") && rule.getTargetField().equals("create_by")) {
            Map<String, Object> personalEmployeeMap = databaseManager1.queryForeignKeyRecord(rule.getForeignKeyTable(), rule.getForeignKeyColumn(), sourceValue);
            Map<String, Object> staffInfoMap = databaseManager1.queryForeignKeyRecord("t_hr_staff_info", "identity_number", sourceValue);
            extracted(targetFieldSet, transformedRow, personalEmployeeMap, staffInfoMap);
            extracted1(targetFieldSet, transformedRow, personalEmployeeMap, staffInfoMap);

//            if (personalEmployeeMap != null){
//                sourceValue = personalEmployeeMap.get(rule.getForeignKeyTargetColumn());
//            }
            if (personalEmployeeMap == null){
                sourceValue = "未找到对应数据";
            }
        } else {
            sourceValue = databaseManager1.queryForeignKeyValue(rule.getForeignKeyTable(), rule.getForeignKeyColumn(), rule.getForeignKeyTargetColumn(), sourceValue);
        }
        return sourceValue;
    }

    private static void extracted(Set<String> targetFieldSet, Map<String, Object> transformedRow, Map<String, Object> personalEmployeeMap, Map<String, Object> staffInfoMap) {
        if (targetFieldSet.contains("agent_id") && personalEmployeeMap != null){
            transformedRow.put("agent_id", personalEmployeeMap.get("id"));
        }

        if (targetFieldSet.contains("agent") && personalEmployeeMap != null){

            transformedRow.put("agent", personalEmployeeMap.get("name"));
        }
        if (targetFieldSet.contains("agent_department_id") && personalEmployeeMap != null){

            transformedRow.put("agent_department_id", personalEmployeeMap.get("department_id"));
        }
        if (targetFieldSet.contains("agent_department") && staffInfoMap != null){

            transformedRow.put("agent_department", staffInfoMap.get("department"));
        }
        if (targetFieldSet.contains("business_agent_id") && personalEmployeeMap != null){

            transformedRow.put("business_agent_id", personalEmployeeMap.get("id"));
        }

        if (targetFieldSet.contains("business_agent_name") && personalEmployeeMap != null)
            transformedRow.put("business_agent_name", personalEmployeeMap.get("name"));
        if (targetFieldSet.contains("business_department_code") && personalEmployeeMap != null)
            transformedRow.put("business_department_code", personalEmployeeMap.get("sys_org_code"));
        if (targetFieldSet.contains("business_department_name") && staffInfoMap != null)
            transformedRow.put("business_department_name", staffInfoMap.get("department"));
        if (targetFieldSet.contains("belong_company_code") && personalEmployeeMap != null)
            transformedRow.put("belong_company_code", personalEmployeeMap.get("sys_org_code"));
        if (targetFieldSet.contains("belong_company") && staffInfoMap != null)
            transformedRow.put("belong_company", staffInfoMap.get("department"));
        if (targetFieldSet.contains("create_by") && personalEmployeeMap != null)
            transformedRow.put("create_by", personalEmployeeMap.get("id"));
        if (targetFieldSet.contains("create_by_name") && personalEmployeeMap != null)
            transformedRow.put("create_by_name", personalEmployeeMap.get("name"));
        if (targetFieldSet.contains("create_time")) transformedRow.put("create_time", new Date());
        if (targetFieldSet.contains("update_by") && personalEmployeeMap != null)
            transformedRow.put("update_by", personalEmployeeMap.get("id"));
        if (targetFieldSet.contains("update_by_name") && personalEmployeeMap != null)
            transformedRow.put("update_by_name", personalEmployeeMap.get("name"));
        if (targetFieldSet.contains("update_department_code") && personalEmployeeMap != null)
            transformedRow.put("update_department_code", personalEmployeeMap.get("sys_org_code"));
        if (targetFieldSet.contains("update_department_name") && staffInfoMap != null)
            transformedRow.put("update_department_name", staffInfoMap.get("department"));
        if (targetFieldSet.contains("update_time"))
            transformedRow.put("update_time", new Date());
        if (targetFieldSet.contains("sys_org_code") && personalEmployeeMap != null)
            transformedRow.put("update_department_code", personalEmployeeMap.get("sys_org_code"));
        if (targetFieldSet.contains("sys_org_name") && staffInfoMap != null)
            transformedRow.put("sys_org_name", staffInfoMap.get("department"));
        if (targetFieldSet.contains("del_flag")) transformedRow.put("del_flag", 0);
        if (targetFieldSet.contains("department_path_id") && staffInfoMap != null) transformedRow.put("department_path_id", staffInfoMap.get("department_path_id"));
        if (targetFieldSet.contains("department_path") && staffInfoMap != null) transformedRow.put("department_path", staffInfoMap.get("department_path"));

        if (targetFieldSet.contains("staff_identity_number") && staffInfoMap != null) transformedRow.put("staff_identity_number", staffInfoMap.get("staff_identity_number"));
        if (targetFieldSet.contains("identity_number") && staffInfoMap != null) transformedRow.put("identity_number", staffInfoMap.get("identity_number"));
        if (targetFieldSet.contains("hobby") && staffInfoMap != null) transformedRow.put("hobby", staffInfoMap.get("skills"));
        if (targetFieldSet.contains("account_identity_number") && staffInfoMap != null) transformedRow.put("account_identity_number", staffInfoMap.get("account_identity_number"));
    }


    private static void extracted1(Set<String> targetFieldSet, Map<String, Object> transformedRow, Map<String, Object> personalEmployeeMap, Map<String, Object> staffInfoMap) {
        if (targetFieldSet.contains("agent_id") && personalEmployeeMap == null){
            transformedRow.put("agent_id", null);
        }

        if (targetFieldSet.contains("agent") && personalEmployeeMap == null){

            transformedRow.put("agent", null);
        }
        if (targetFieldSet.contains("agent_department_id") && personalEmployeeMap == null){

            transformedRow.put("agent_department_id", null);
        }
        if (targetFieldSet.contains("agent_department") && staffInfoMap == null){

            transformedRow.put("agent_department", null);
        }
        if (targetFieldSet.contains("business_agent_id") && personalEmployeeMap == null){

            transformedRow.put("business_agent_id", null);
        }

        if (targetFieldSet.contains("business_agent_name") && personalEmployeeMap == null)
            transformedRow.put("business_agent_name", null);
        if (targetFieldSet.contains("business_department_code") && personalEmployeeMap == null)
            transformedRow.put("business_department_code", null);
        if (targetFieldSet.contains("business_department_name") && staffInfoMap == null)
            transformedRow.put("business_department_name", null);
        if (targetFieldSet.contains("belong_company_code") && personalEmployeeMap == null)
            transformedRow.put("belong_company_code", null);
        if (targetFieldSet.contains("belong_company") && staffInfoMap == null)
            transformedRow.put("belong_company", null);
        if (targetFieldSet.contains("create_by") && personalEmployeeMap == null)
            transformedRow.put("create_by", "找不到关联数据");
        if (targetFieldSet.contains("create_by_name") && personalEmployeeMap == null)
            transformedRow.put("create_by_name", null);
        if (targetFieldSet.contains("create_time")) transformedRow.put("create_time", new Date());
        if (targetFieldSet.contains("update_by") && personalEmployeeMap == null)
            transformedRow.put("update_by", null);
        if (targetFieldSet.contains("update_by_name") && personalEmployeeMap == null)
            transformedRow.put("update_by_name", null);
        if (targetFieldSet.contains("update_department_code") && personalEmployeeMap == null)
            transformedRow.put("update_department_code", null);
        if (targetFieldSet.contains("update_department_name") && staffInfoMap == null)
            transformedRow.put("update_department_name", null);
        if (targetFieldSet.contains("update_time"))
            transformedRow.put("update_time", new Date());
        if (targetFieldSet.contains("sys_org_code") && personalEmployeeMap == null)
            transformedRow.put("update_department_code", null);
        if (targetFieldSet.contains("sys_org_name") && staffInfoMap == null)
            transformedRow.put("sys_org_name", null);
        if (targetFieldSet.contains("del_flag")) transformedRow.put("del_flag", 0);
        if (targetFieldSet.contains("department_path_id") && staffInfoMap == null) transformedRow.put("department_path_id", null);
        if (targetFieldSet.contains("department_path") && staffInfoMap == null) transformedRow.put("department_path", null);
        if (targetFieldSet.contains("staff_identity_number") && staffInfoMap == null) transformedRow.put("staff_identity_number", null);
        if (targetFieldSet.contains("identity_number") && staffInfoMap == null) transformedRow.put("identity_number", null);
        if (targetFieldSet.contains("hobby") && staffInfoMap == null) transformedRow.put("hobby", null);
        if (targetFieldSet.contains("account_identity_number") && staffInfoMap == null) transformedRow.put("account_identity_number", null);
    }

    private ArrayList<String> ignoreColumns() {
        ArrayList<String> ignoreColumns = new ArrayList<>();
        ignoreColumns.add("agent_id");
        ignoreColumns.add("agent");
        ignoreColumns.add("agent_department_id");
        ignoreColumns.add("agent_department");
        ignoreColumns.add("business_agent_id");
        ignoreColumns.add("business_agent_name");
        ignoreColumns.add("business_department_code");
        ignoreColumns.add("business_department_name");
        ignoreColumns.add("belong_company_code");
        ignoreColumns.add("belong_company");
        ignoreColumns.add("create_by_name");
        ignoreColumns.add("create_time");
        ignoreColumns.add("update_by");
        ignoreColumns.add("update_by_name");
        ignoreColumns.add("update_department_code");
        ignoreColumns.add("update_department_name");
        ignoreColumns.add("update_time");
        ignoreColumns.add("sys_org_code");
        ignoreColumns.add("sys_org_name");
        ignoreColumns.add("del_flag");
        ignoreColumns.add("department_path_id");
        ignoreColumns.add("department_path");
        ignoreColumns.add("staff_identity_number");
        ignoreColumns.add("identity_number");
        ignoreColumns.add("account_identity_number");
        ignoreColumns.add("hobby");
        return ignoreColumns;
    }

    public Map<String, Object> transformData(Map<String, Object> sourceData, String sourceTable, String targetTable) {
        log.info("开始转换数据，源表：" + sourceTable + ", 目标表：" + targetTable);
        
        try {
            List<MappingRule> rules = new ArrayList<>(); // TODO: 实现正确的映射规则获取逻辑
            Map<String, Object> transformedData = transformRow(sourceData, rules);
            
            log.debug("数据转换完成，结果：" + transformedData);
            return transformedData;
        } catch (Exception e) {
            log.error("数据转换失败，源表：" + sourceTable + ", 目标表：" + targetTable, e);
            throw new RuntimeException("数据转换失败：" + e.getMessage(), e);
        }
    }

    public List<Map<String, Object>> transformData(List<Map<String, Object>> rawData, List<MappingRule> rules) {
        List<Map<String, Object>> transformedData = new ArrayList<>();
        for (Map<String, Object> row : rawData) {
            transformedData.add(transformRow(row, rules));
        }
        return transformedData;
    }

    public Object convertValue(Object value, String targetType) {
        TypeConverter converter = converters.get(targetType);
        if (converter == null) {
            throw new IllegalArgumentException("不支持的转换类型: " + targetType);
        }
        return converter.convert(value, targetType.getClass());
    }

    public Object convertValue(Object value, String targetType, String format) {
        TypeConverter converter = converters.get(targetType);
        if (converter == null) {
            throw new IllegalArgumentException("不支持的转换类型: " + targetType);
        }
        return converter.convert(value, targetType.getClass(), format);
    }

    public String getSqlType(String javaType) {
        switch (javaType) {
            case "Integer":
            case "int":
                return "INT";
            case "Long":
            case "long":
                return "BIGINT";
            case "Double":
            case "double":
                return "DOUBLE";
            case "Float":
            case "float":
                return "FLOAT";
            case "BigDecimal":
                return "DECIMAL(19,4)";
            case "String":
                return "VARCHAR(255)";
            case "LocalDate":
                return "DATE";
            case "LocalDateTime":
                return "DATETIME";
            case "Date":
                return "DATETIME";
            case "Boolean":
            case "boolean":
                return "TINYINT(1)";
            case "Byte":
            case "byte":
                return "TINYINT";
            case "Short":
            case "short":
                return "SMALLINT";
            case "byte[]":
                return "BLOB";
            case "char[]":
                return "TEXT";
            case "Map":
                return "JSON";
            case "List":
                return "JSON";
            case "UUID":
                return "CHAR(36)";
            case "LocalTime":
                return "TIME";
            case "Instant":
                return "TIMESTAMP";
            default:
                return "VARCHAR(255)";
        }
    }

    // 扩展类型转换器接口
    private interface TypeConverter {
        Object convert(Object value, Class<?> targetType);

        default Object convert(Object value, Class<?> targetType, String format) {
            return convert(value, targetType);
        }
    }

    // 扩展整数转换器
    private static class IntegerConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof Number) {
                return ((Number) value).intValue();
            } else if (value instanceof String) {
                return Integer.parseInt((String) value);
            } else if (value instanceof Boolean) {
                return (Boolean) value ? 1 : 0;
            }
            return null;
        }
    }

    // 扩展长整型转换器
    private static class LongConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof Number) {
                return ((Number) value).longValue();
            } else if (value instanceof String) {
                return Long.parseLong((String) value);
            }
            return null;
        }
    }

    // 扩展浮点数转换器
    private static class DoubleConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof Number) {
                return ((Number) value).doubleValue();
            } else if (value instanceof String) {
                return Double.parseDouble((String) value);
            }
            return null;
        }
    }

    // 扩展大精度数转换器
    private static class BigDecimalConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof BigDecimal) {
                return value;
            } else if (value instanceof Number) {
                return new BigDecimal(value.toString());
            } else if (value instanceof String) {
                return new BigDecimal((String) value);
            }
            return null;
        }
    }

    // 新增布尔值转换器
    private static class BooleanConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof Boolean) {
                return value;
            } else if (value instanceof Number) {
                return ((Number) value).intValue() != 0;
            } else if (value instanceof String) {
                return Boolean.parseBoolean((String) value);
            }
            return null;
        }
    }

    // 新增日期时间转换器
    private static class DateConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            return convert(value, targetType, null);
        }

        @Override
        public Object convert(Object value, Class<?> targetType, String format) {
            if (value == null) return null;
            if (value instanceof LocalDate) {
                return (LocalDate) value;
            } else if (value instanceof LocalDateTime) {
                return ((LocalDateTime) value).toLocalDate();
            } else if (value instanceof String) {
                String[] split = value.toString().split(" ");
                if (format != null && !format.isEmpty()) {
                    return LocalDate.parse((String) value, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                }
                return LocalDate.parse(split[0]);
            } else if (value instanceof Date) {
                return ((Date) value).toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDate();
            }
            return null;
        }
    }

    // 新增日期时间转换器
    private static class DateTimeConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            return convert(value, targetType, null);
        }

        @Override
        public Object convert(Object value, Class<?> targetType, String format) {
            if (value == null) return null;
            if (value instanceof LocalDateTime) {
                return value;
            } else if (value instanceof LocalDate) {
                return ((LocalDate) value).atStartOfDay();
            } else if (value instanceof String) {
                format = format != null && !format.isEmpty() ? format : "yyyy-MM-dd HH:mm:ss";
                if (value.toString().contains(".0")){
                    value = value.toString().replace(".0", "");
                }
                if (format != null && !format.isEmpty()) {
                    return LocalDateTime.parse((String) value, DateTimeFormatter.ofPattern(format));
                }
                return LocalDateTime.parse((String) value);
            } else if (value instanceof Date) {
                return ((Date) value).toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDateTime();
            }
            return null;
        }
    }

    // 新增字符串转换器
    private static class StringConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            return value.toString();
        }
    }

    // 新增二进制转换器
    private static class BinaryConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof byte[]) {
                return value;
            } else if (value instanceof String) {
                return ((String) value).getBytes();
            }
            return null;
        }
    }

    // 新增JSON转换器
    private static class JsonConverter implements TypeConverter {
        private final ObjectMapper objectMapper = new ObjectMapper();

        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            try {
                if (value instanceof String) {
                    return value;
                } else if (value instanceof Map || value instanceof List) {
                    return objectMapper.writeValueAsString(value);
                }
            } catch (JsonProcessingException e) {
                throw new RuntimeException("JSON转换失败", e);
            }
            return null;
        }
    }

    // 新增时间转换器
    private static class TimeConverter implements TypeConverter {
        @Override
        public Object convert(Object value, Class<?> targetType) {
            if (value == null) return null;
            if (value instanceof LocalTime) {
                return value;
            } else if (value instanceof String) {
                return LocalTime.parse((String) value);
            }
            return null;
        }
    }

    /**
     * 将字符串转换为MD5哈希值
     * @param input 输入字符串
     * @return MD5哈希值
     */
    private String md5Hash(String input) {
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] messageDigest = md.digest(input.getBytes());
            StringBuilder sb = new StringBuilder();
            for (byte b : messageDigest) {
                sb.append(String.format("%02x", b));
            }
            return sb.toString();
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException("MD5算法不可用", e);
        }
    }
}